use greenlife
go

create proc selectNews
as
begin
	select * from News
end

go
create proc addNews
@Title nvarchar(100),
@QuickTitle nvarchar(100),
@Images varchar(max),
@Contents nvarchar(max),
@NStatus int
as
begin
	insert into News(Title,QuickTitle,Images,Contents,NStatus) values(@Title,@QuickTitle,@Images,@Contents,@NStatus)
end
go

create proc selectNewsByID
@ID int
as
begin
	select * from News where NewsID=@ID
end

go

create proc updateNews
@NewsID int,
@Title nvarchar(100),
@QuickTitle nvarchar(100),
@Images varchar(max),
@Contents nvarchar(max),
@NStatus int
as
begin
	update News set Title=@Title,QuickTitle=@QuickTitle,Images=@Images,Contents=@Contents,NStatus=@NStatus where NewsID=@NewsID
end
go
create proc removeNews
@NewsID int
as
begin
	delete from News where NewsID=@NewsID
end
go
create proc selectTopNewsByStatus
@NStatus int
as
begin

	select TOP 1 * from News where NStatus=@NStatus order by NewsID DESC
end
go
create proc selectNewsByStatus
@NStatus int
as
begin

	select Top 5 * from News where NStatus=@NStatus order by NewsID DESC
end
--tao phan trang cho bang news
go
create PROCEDURE [dbo].[spNews_PhanTrang]
@currPage int,
@recodperpage int,
@Pagesize int,
@Status int
AS
Begin
    Begin
    WITH s AS
    (
        SELECT ROW_NUMBER()
                        OVER(ORDER BY NewsID,
                        Title) AS RowNum,
                        NewsID,
                        Title,
                        QuickTitle, Images,Contents,NewsDate,NStatus
        FROM News where NStatus =@Status
    )
    Select * From s
    Where RowNum Between
                (@currPage - 1)*@recodperpage+1
                        AND @currPage*@recodperpage
    END
    -- Tinh tong so ban ghi
    DECLARE @Tolal int
    SELECT @Tolal=Count(*) FROM dbo.News where NStatus =@Status
    EXEC spPhanTrangSQL 
			@Tolal, 
			@currPage, 
			@Pagesize, 
			@recodperpage
END
--bang customer
go 
create proc selectCategory
as
begin
	select * from Category
end

go
create proc addCategory
@CategoryName nvarchar(50),
@CategoryType nvarchar(20),
@CategoryDescription nvarchar(250)
as
begin
	insert into Category values(@CategoryName,@CategoryType,@CategoryDescription)
end

go
create proc selectCategoryByID
@CategoryID int
as
begin
	select * from Category where CategoryID =@CategoryID
end

go
create proc selectCategoryByType
@CategoryType nvarchar(20)
as
begin
	select * from Category where CategoryType =@CategoryType
end
go
create proc updateCategory
@CategoryID int,
@CategoryName nvarchar(50),
@CategoryType nvarchar(20),
@CategoryDescription nvarchar(250)
as
begin
	update Category set CategoryName=@CategoryName,CategoryType=@CategoryType,CategoryDescription=@CategoryDescription where CategoryID=@CategoryID
end
go
create proc removeCategory
@CategoryID int
as
begin
	delete from Category where CategoryID=@CategoryID
end


--bang SubCategory
go
create proc selectSubCategory
as
begin
	select * from SubCategory
end

go
create proc addSubCategory
@CategoryId int,
@SubCategoryName nvarchar(50),
@SubCategoryImage varchar(max),
@SubCategoryDescription nvarchar(max),
@SubCategoryArea float,
@SubCategoryHuong nvarchar(50)
as
begin
	insert into SubCategory values(@CategoryId,@SubCategoryName,@SubCategoryImage,@SubCategoryDescription,@SubCategoryArea,@SubCategoryHuong)
end

go
create proc selectSubCategoryById
@SubCategoryId int
as
begin
	select * from SubCategory where SubCategoryId=@SubCategoryId
end
go
create proc selectSubCategoryByCateId
@CategoryID int
as
begin
	select * from SubCategory where CategoryID=@CategoryID
end
go
create proc updateSubCategory
@SubCategoryId int,
@CategoryId int,
@SubCategoryName nvarchar(50),
@SubCategoryImage varchar(max),
@SubCategoryDescription nvarchar(max),
@SubCategoryArea float,
@SubCategoryHuong nvarchar(50)
as
begin
	update SubCategory set CategoryId=@CategoryId,SubCategoryName=@SubCategoryName,SubCategoryImage=@SubCategoryImage,SubCategoryDescription=@SubCategoryDescription,
	@SubCategoryArea=SubCategoryArea,@SubCategoryHuong=SubCategoryHuong where SubCategoryId=@SubCategoryId
end

go
create proc removeSubCategory
@SubCategoryId int
as
begin
	delete from SubCategory where SubCategoryId=@SubCategoryId
end

--bang GreenLifes
go
create proc selectGreenLifes
as
begin
	select * from GreenLifes
end

go
create proc addGreenLifes
@GreenLifesTitle nvarchar(100),
@GreenLifesQuickTitle nvarchar(150),
@GreenLifesImages varchar(max),
@GreenLifesContents nvarchar(max),
@GreenLifesStatus int
as
begin
	insert into GreenLifes values(@GreenLifesTitle,@GreenLifesQuickTitle,@GreenLifesImages,@GreenLifesContents,@GreenLifesStatus)
end

go
create proc selectGreenLifesById
@GreenLifesID int
as
begin
	select * from GreenLifes where GreenLifesID=@GreenLifesID 
end

go
create proc updateGreenLifes
@GreenLifesID int,
@GreenLifesTitle nvarchar(100),
@GreenLifesQuickTitle nvarchar(150),
@GreenLifesImages varchar(max),
@GreenLifesContents nvarchar(max),
@GreenLifesStatus int
as
begin
	update GreenLifes set GreenLifesTitle=@GreenLifesTitle,GreenLifesQuickTitle=@GreenLifesQuickTitle,GreenLifesImages=@GreenLifesImages,
	GreenLifesContents=@GreenLifesContents,GreenLifesStatus=@GreenLifesStatus where GreenLifesID=@GreenLifesID
end

go
create proc removeGreenLifes
@GreenLifesID int
as
begin
	delete from GreenLifes where GreenLifesID=@GreenLifesID
end

go
create proc selectGreenLifesByStatus
@GreenLifesStatus int
as
begin
	select * from GreenLifes where GreenLifesStatus=@GreenLifesStatus 
end
--tao phan trang cho bang GreenLife
go
create PROCEDURE [dbo].[spGreenLife_PhanTrang]
@currPage int,
@recodperpage int,
@Pagesize int
AS
Begin
    Begin
    WITH s AS
    (
        SELECT ROW_NUMBER()
                        OVER(ORDER BY GreenLifesID,
                        GreenLifesTitle) AS RowNum,
                        GreenLifesID,
                        GreenLifesTitle,
                        GreenLifesQuickTitle, GreenLifesImages,GreenLifesContents,GreenLifesStatus
        FROM dbo.GreenLifes
    )
    Select * From s
    Where RowNum Between
                (@currPage - 1)*@recodperpage+1
                        AND @currPage*@recodperpage
    END
    -- Tinh tong so ban ghi
    DECLARE @Tolal int
    SELECT @Tolal=Count(*) FROM dbo.GreenLifes
    EXEC spPhanTrangSQL 
			@Tolal, 
			@currPage, 
			@Pagesize, 
			@recodperpage
END
go
create PROCEDURE [dbo].[spGreenLifeAll_PhanTrang]
@currPage int,
@recodperpage int,
@Pagesize int,
@Status int
AS
Begin
    Begin
    WITH s AS
    (
        SELECT ROW_NUMBER()
                        OVER(ORDER BY GreenLifesID,
                        GreenLifesTitle) AS RowNum,
                        GreenLifesID,
                        GreenLifesTitle,
                        GreenLifesQuickTitle, GreenLifesImages,GreenLifesContents,GreenLifesStatus
        FROM dbo.GreenLifes where  GreenLifesStatus=@Status
    )
    Select * From s
    Where RowNum Between
                (@currPage - 1)*@recodperpage+1
                        AND @currPage*@recodperpage
    END
    -- Tinh tong so ban ghi
    DECLARE @Tolal int
    SELECT @Tolal=Count(*) FROM dbo.GreenLifes where GreenLifesStatus =@Status
    EXEC spPhanTrangSQL 
			@Tolal, 
			@currPage, 
			@Pagesize, 
			@recodperpage
END
go
create proc selectTopGreenLifesByStatus
@GreenLifesStatus int
as
begin
	select top 1 * from GreenLifes where GreenLifesStatus=@GreenLifesStatus order by GreenLifesID DESC
end
--bang Users
go
create proc selectUsers
as
begin
	select * from Users
end

go
create proc addUsers
@UserName varchar(50),
@Password varchar(40),
@UserStatus int
as
begin
	insert into Users values(@UserName,@Password,@UserStatus)
end

go
create proc selectUserByName
@UserName varchar(50)
as
begin
	select * from Users where UserName=@UserName
end

go
create proc updateUsers
@UserName varchar(50),
@Password varchar(40),
@UserStatus int
as
begin
	update Users set Passwords=@Password,UsersStatus=@UserStatus where UserName=@UserName
end
go
create proc resetPass
@UserName varchar(50),
@Password varchar(40)
as
begin
	update Users set Passwords=@Password where UserName=@UserName
end
go
create proc removeUsers
@UserName varchar(50)
as
begin
	delete from Users where UserName=@UserName
end

go
-- phan trang user
create PROCEDURE [dbo].[spUser_PhanTrang]
@currPage int,
@recodperpage int,
@Pagesize int
AS
Begin
    Begin
    WITH s AS
    (
        SELECT ROW_NUMBER()
                        OVER(ORDER BY UserName) AS RowNum,
                        UserName,
                        Passwords,
                        UsersStatus
        FROM dbo.Users
    )
    Select * From s
    Where RowNum Between
                (@currPage - 1)*@recodperpage+1
                        AND @currPage*@recodperpage
    END
    -- Tinh tong so ban ghi
    DECLARE @Tolal int
    SELECT @Tolal=Count(*) FROM  dbo.Users
    EXEC spPhanTrangSQL 
			@Tolal, 
			@currPage, 
			@Pagesize, 
			@recodperpage
END


--bang Customer
go
create proc selectCustomer
as
begin
	select * from Customer
end

go
create proc addCustomer
@CustomerName nvarchar(50),
@CustomerPhone nvarchar(15),
@CustomerEmail nvarchar(150)
as
begin
	insert into Customer values(@CustomerName,@CustomerPhone,@CustomerEmail)
end

go
create proc selectCustomerById
@CustomerID int
as
begin
	select * from Customer where CustomerID=@CustomerID
end

go
create proc updateCustomer
@CustomerID int,
@CustomerName nvarchar(50),
@CustomerPhone nvarchar(15),
@CustomerEmail nvarchar(150)
as
begin
	update Customer set CustomerName=@CustomerName,CustomerPhone=@CustomerPhone,CustomerEmail=@CustomerEmail
	where CustomerID=@CustomerID
end

go
create proc removeCustomer
@CustomerID int
as
begin
	delete from Customer where CustomerID=@CustomerID 
end

--bang Order
go
create proc selectOrder
as
begin
	select * from Orders
end

go
create proc addOrder
@CustomerID int,
@SubCategoryID int,
@CreateDate datetime
as
begin 
	insert into Orders values(@CustomerID,@SubCategoryID,@CreateDate)
end

go
create proc selectOrderById
@OrdersID int
as
begin 
	select * from Orders where OrdersID=@OrdersID
end

go
create proc updateOrder
@OrdersID int,
@CustomerID int,
@SubCategoryID int,
@CreateDate datetime
as
begin
	update Orders set CustomerID=@CustomerID,SubCategoryID=@SubCategoryID,CreateDate=@CreateDate 
	where OrdersID=@OrdersID
end

go
create proc removeOrder
@OrdersID int
as
begin
	delete from Orders where OrdersID=@OrdersID
end

--bang LinkWeb
go
create proc selectLinkWeb
as
begin
	select * from LinkWeb
end

go
create proc addLinkWeb
@LinkWebName nvarchar(100),
@LinkWebSite nvarchar(100)
as
begin
	insert into LinkWeb values(@LinkWebName,@LinkWebSite)
end

go
create proc selectLinkWebById
@LinkWebID int
as
begin
	select * from LinkWeb where LinkWebID=@LinkWebID
end

go
create proc updateLinkWeb
@LinkWebID int,
@LinkWebName nvarchar(100),
@LinkWebSite nvarchar(100)
as
begin
	update LinkWeb set LinkWebName=@LinkWebName,LinkWebSite=@LinkWebSite where LinkWebID=@LinkWebID
end

go
create proc removeLinkWeb
@LinkWebID int
as
begin
	delete LinkWeb where LinkWebID=@LinkWebID	
end
--tao phan trang cho bang LinkWeb
go
create PROCEDURE [dbo].[spLinkWeb_PhanTrang]
@currPage int,
@recodperpage int,
@Pagesize int
AS
Begin
    Begin
    WITH s AS
    (
        SELECT ROW_NUMBER()
                        OVER(ORDER BY LinkWebID) 
						AS RowNum,
                        LinkWebID,
                        LinkWebName,
                        LinkWebSite
        FROM LinkWeb
    )
    Select * From s
    Where RowNum Between
                (@currPage - 1)*@recodperpage+1
                        AND @currPage*@recodperpage
    END
    -- Tinh tong so ban ghi
    DECLARE @Tolal int
    SELECT @Tolal=Count(*) FROM dbo.LinkWeb
    EXEC spPhanTrangSQL 
			@Tolal, 
			@currPage, 
			@Pagesize, 
			@recodperpage
END
